#!/usr/local/bin/perl -w

use App::Options (
    options => [qw(dbdriver dbclass dbhost dbname dbuser dbpass)],
    option => {
        dbclass  => { default => "App::Repository::MySQL", },
        dbdriver => { default => "mysql", },
        dbhost   => { default => "localhost", },
        dbname   => { default => "test", },
        dbuser   => { default => "", },
        dbpass   => { default => "", },
        id       => { description => "The number of the test to run", },
    },
);

use Test::More qw(no_plan);
use lib "../App-Context/lib";
use lib "../../App-Context/lib";
use lib "lib";
use lib "../lib";
use lib ".";
use lib "t";

use App;
use App::Repository;
use RepositoryTestUtils qw(create_table_test_person drop_table_test_person populate_table_test_person);
use strict;

my $dbtype = $App::options{dbtype} || "mysql";

if (!$App::options{dbuser}) {
    ok(1, "No dbuser given. Tests assumed OK. (add dbuser=xxx and dbpass=yyy to app.conf in 't' directory)");
    exit(0);
}

my $context = App->context(
    conf_file => "",
    conf => {
        Repository => {
            default => {
                class => $App::options{dbclass},
                dbdriver => $App::options{dbdriver},
                dbhost => $App::options{dbhost},
                dbname => $App::options{dbname},
                dbuser => $App::options{dbuser},
                dbpass => $App::options{dbpass},
                table => {
                    test_person => {
                        primary_key => ["person_id"],
                        alias => "p",
                        column => {
                            gender => {
                                alias => "gnd",
                            },
                        },
                    },
                },
                default_date_format => "YYYY-MM-DD HH24:MI:SS",
            },
        },
    },
    debug_sql => $App::options{debug_sql},
    trace => $App::options{trace},
);

my $rep = $context->repository();
&drop_table_test_person($rep);
&create_table_test_person($rep);
&populate_table_test_person($rep);

sub check_select {
    my ($sql, $expected_rows, $id0, $idn, $msg, $debug) = @_;

    my ($rows, $reprows);
    eval {
        $rows = $rep->_do($sql);
    };
    is($@,"","$msg : sql executed ok");
    if ($debug) {
        print $sql;
        print "ROWS [", ($#$rows + 1), "]\n";
        foreach my $row (@$rows) {
            print "ROW [", join("|", @$row), "]\n";
        }
    }

    if (defined $expected_rows) {
        is(($#$rows + 1), $expected_rows, "$msg : nrows=$expected_rows (from raw select)");
    }

    if (defined $id0) {
        if ($#$rows == -1) {
            ok(0, "$msg : no rows when searching for a starting ID");
        }
        else {
            is($rows->[0][0], $id0, "$msg : first row id was $id0");
        }
    }

    if (defined $idn) {
        if ($#$rows == -1) {
            ok(0, "$msg : no rows when searching for a final ID");
        }
        else {
            is($rows->[$#$rows][0], $idn, "$msg : last row id was $idn");
        }
    }
}

# &test_get_rows($expect_sql,0,"_mk_select_joined_sql(): 1 col, no params","test_person",{},"age");
sub test_get_rows {
    my ($expected_sql, $nrows, $id0, $idn, $msg, $table, $params, $cols, $options) = @_;

    my ($rows, $sql);
    eval {
        $rows = $rep->get_rows($table, $params, $cols, $options);
    };
    is($@,"","$msg : get_rows() ok");
    $sql = $rep->{sql};
    is($sql, $expected_sql, "$msg : get_rows() sql ok");
    is($#$rows + 1, $nrows, "$msg : nrows=$nrows (from get_rows())") if (defined $nrows);

    if (defined $id0) {
        if ($#$rows == -1) {
            ok(0, "$msg : no rows when searching for a starting ID");
        }
        else {
            is($rows->[0][0], $id0, "$msg : first row id was $id0");
        }
    }

    if (defined $idn) {
        if ($#$rows == -1) {
            ok(0, "$msg : no rows when searching for a final ID");
        }
        else {
            is($rows->[$#$rows][0], $idn, "$msg : last row id was $idn");
        }
    }
}

my ($sql, $expect_sql);
my @options = (
    { startrow => 1,     endrow => 1, nrows => 1, id0 => 1,     idn => 1,
      oracle_where_0 => "", oracle_prefix_0 => "select a.* from\n(", oracle_suffix_0 => "\n) a where rownum <= 1",
      oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(", oracle_suffix_1 => "\n) a where rownum <= 1",
      mysql_suffix_0 => "\nlimit 1",
      mysql_suffix_1 => "\nlimit 1",
    },
    { startrow => 2,     endrow => 2, nrows => 1, id0 => 2,     idn => 2,
      oracle_where_0 => "", oracle_prefix_0 => "select * from (select a.*, rownum x_rownum from\n(", oracle_suffix_0 => "\n) a where rownum <= 2) where x_rownum >= 2",
      oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(", oracle_suffix_1 => "\n) a where rownum <= 2",
      mysql_suffix_0 => "\nlimit 1 offset 1",
      mysql_suffix_1 => "\nlimit 2",
    },
    { startrow => 3,     endrow => 3, nrows => 1, id0 => 3,     idn => 3,
      oracle_where_0 => "", oracle_prefix_0 => "select * from (select a.*, rownum x_rownum from\n(", oracle_suffix_0 => "\n) a where rownum <= 3) where x_rownum >= 3",
      oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(", oracle_suffix_1 => "\n) a where rownum <= 3",
      mysql_suffix_0 => "\nlimit 1 offset 2",
      mysql_suffix_1 => "\nlimit 3",
    },
    { startrow => 3, nrows => 5, id0 => 3,     idn => 7,
      oracle_where_0 => "", oracle_prefix_0 => "select * from (select a.*, rownum x_rownum from\n(", oracle_suffix_0 => "\n) a) where x_rownum >= 3",
      oracle_where_1 => "", oracle_prefix_1 => "", oracle_suffix_1 => "",
      mysql_suffix_0 => "\nlimit 999999999 offset 2",
      mysql_suffix_1 => "",
    },
    { startrow => 3,     endrow => 2, nrows => 0, id0 => undef, idn => undef,
      oracle_where_0 => "\nwhere rownum <= 0", oracle_prefix_0 => "", oracle_suffix_0 => "",
      oracle_where_1 => "\nwhere rownum <= 0", oracle_prefix_1 => "", oracle_suffix_1 => "",
      mysql_suffix_0 => "\nlimit 0",
      mysql_suffix_1 => "\nlimit 0",
    },
    {                    endrow => 3, nrows => 3, id0 => 1,     idn => 3,
      oracle_where_0 => "", oracle_prefix_0 => "select a.* from\n(", oracle_suffix_0 => "\n) a where rownum <= 3",
      oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(", oracle_suffix_1 => "\n) a where rownum <= 3",
      mysql_suffix_0 => "\nlimit 3",
      mysql_suffix_1 => "\nlimit 3",
    },
    { startrow => 0,     endrow => 3, nrows => 3, id0 => 1,     idn => 3,
      oracle_where_0 => "", oracle_prefix_0 => "select a.* from\n(", oracle_suffix_0 => "\n) a where rownum <= 3",
      oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(", oracle_suffix_1 => "\n) a where rownum <= 3",
      mysql_suffix_0 => "\nlimit 3",
      mysql_suffix_1 => "\nlimit 3",
    },
    { startrow => 1,     endrow => 3, nrows => 3, id0 => 1,     idn => 3,
      oracle_where_0 => "", oracle_prefix_0 => "select a.* from\n(", oracle_suffix_0 => "\n) a where rownum <= 3",
      oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(", oracle_suffix_1 => "\n) a where rownum <= 3",
      mysql_suffix_0 => "\nlimit 3",
      mysql_suffix_0 => "\nlimit 3",
      mysql_suffix_1 => "\nlimit 3",
    },
    { startrow => 2,     endrow => 3, nrows => 2, id0 => 2,     idn => 3,
      oracle_where_0 => "", oracle_prefix_0 => "select * from (select a.*, rownum x_rownum from\n(", oracle_suffix_0 => "\n) a where rownum <= 3) where x_rownum >= 2",
      oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(", oracle_suffix_1 => "\n) a where rownum <= 3",
      mysql_suffix_0 => "\nlimit 2 offset 1",
      mysql_suffix_1 => "\nlimit 3",
    },
    {                    endrow => 7, nrows => 7, id0 => 1,     idn => 7,
      oracle_where_0 => "", oracle_prefix_0 => "select a.* from\n(", oracle_suffix_0 => "\n) a where rownum <= 7",
      oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(", oracle_suffix_1 => "\n) a where rownum <= 7",
      mysql_suffix_0 => "\nlimit 7",
      mysql_suffix_1 => "\nlimit 7",
    },
    { startrow => 0,     endrow => 7, nrows => 7, id0 => 1,     idn => 7,
      oracle_where_0 => "", oracle_prefix_0 => "select a.* from\n(", oracle_suffix_0 => "\n) a where rownum <= 7",
      oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(", oracle_suffix_1 => "\n) a where rownum <= 7",
      mysql_suffix_0 => "\nlimit 7",
      mysql_suffix_1 => "\nlimit 7",
    },
    { startrow => 1,     endrow => 7, nrows => 7, id0 => 1,     idn => 7,
      oracle_where_0 => "", oracle_prefix_0 => "select a.* from\n(", oracle_suffix_0 => "\n) a where rownum <= 7",
      oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(", oracle_suffix_1 => "\n) a where rownum <= 7",
      mysql_suffix_0 => "\nlimit 7",
      mysql_suffix_1 => "\nlimit 7",
    },
    { startrow => 3,     endrow => 7, nrows => 5, id0 => 3,     idn => 7,
      oracle_where_0 => "", oracle_prefix_0 => "select * from (select a.*, rownum x_rownum from\n(", oracle_suffix_0 => "\n) a where rownum <= 7) where x_rownum >= 3",
      oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(", oracle_suffix_1 => "\n) a where rownum <= 7",
      mysql_suffix_0 => "\nlimit 5 offset 2",
      mysql_suffix_1 => "\nlimit 7",
    },
    { startrow => 7,     endrow => 7, nrows => 1, id0 => 7,     idn => 7,
      oracle_where_0 => "", oracle_prefix_0 => "select * from (select a.*, rownum x_rownum from\n(", oracle_suffix_0 => "\n) a where rownum <= 7) where x_rownum >= 7",
      oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(", oracle_suffix_1 => "\n) a where rownum <= 7",
      mysql_suffix_0 => "\nlimit 1 offset 6",
      mysql_suffix_1 => "\nlimit 7",
    },
    {                    endrow => 8, nrows => 7, id0 => 1,     idn => 7,
      oracle_where_0 => "", oracle_prefix_0 => "select a.* from\n(", oracle_suffix_0 => "\n) a where rownum <= 8",
      oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(", oracle_suffix_1 => "\n) a where rownum <= 8",
      mysql_suffix_0 => "\nlimit 8",
      mysql_suffix_1 => "\nlimit 8",
    },
    { startrow => 0,     endrow => 8, nrows => 7, id0 => 1,     idn => 7,
      oracle_where_0 => "", oracle_prefix_0 => "select a.* from\n(", oracle_suffix_0 => "\n) a where rownum <= 8",
      oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(", oracle_suffix_1 => "\n) a where rownum <= 8",
      mysql_suffix_0 => "\nlimit 8",
      mysql_suffix_1 => "\nlimit 8",
    },
    { startrow => 1,     endrow => 8, nrows => 7, id0 => 1,     idn => 7,
      oracle_where_0 => "", oracle_prefix_0 => "select a.* from\n(", oracle_suffix_0 => "\n) a where rownum <= 8",
      oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(", oracle_suffix_1 => "\n) a where rownum <= 8",
      mysql_suffix_0 => "\nlimit 8",
      mysql_suffix_1 => "\nlimit 8",
    },
    { startrow => 3,     endrow => 8, nrows => 5, id0 => 3,     idn => 7,
      oracle_where_0 => "", oracle_prefix_0 => "select * from (select a.*, rownum x_rownum from\n(", oracle_suffix_0 => "\n) a where rownum <= 8) where x_rownum >= 3",
      oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(", oracle_suffix_1 => "\n) a where rownum <= 8",
      mysql_suffix_0 => "\nlimit 6 offset 2",
      mysql_suffix_1 => "\nlimit 8",
    },
    { startrow => 7,     endrow => 8, nrows => 1, id0 => 7,     idn => 7,
      oracle_where_0 => "", oracle_prefix_0 => "select * from (select a.*, rownum x_rownum from\n(", oracle_suffix_0 => "\n) a where rownum <= 8) where x_rownum >= 7",
      oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(", oracle_suffix_1 => "\n) a where rownum <= 8",
      mysql_suffix_0 => "\nlimit 2 offset 6",
      mysql_suffix_1 => "\nlimit 8",
    },
    { startrow => 0,     endrow => 0, nrows => 7, id0 => 1,     idn => 7,
      oracle_where_0 => "", oracle_prefix_0 => "", oracle_suffix_0 => "",
      oracle_where_1 => "", oracle_prefix_1 => "", oracle_suffix_1 => "",
      mysql_suffix_0 => "",
      mysql_suffix_1 => "",
    },
    { startrow => undef, endrow => 0, nrows => 7, id0 => 1,     idn => 7,
      oracle_where_0 => "", oracle_prefix_0 => "", oracle_suffix_0 => "",
      oracle_where_1 => "", oracle_prefix_1 => "", oracle_suffix_1 => "",
      mysql_suffix_0 => "",
      mysql_suffix_1 => "",
    },
    { startrow => 0,     endrow => undef, nrows => 7, id0 => 1,     idn => 7,
      oracle_where_0 => "", oracle_prefix_0 => "", oracle_suffix_0 => "",
      oracle_where_1 => "", oracle_prefix_1 => "", oracle_suffix_1 => "",
      mysql_suffix_0 => "",
      mysql_suffix_1 => "",
    },
    { startrow => undef, endrow => undef, nrows => 7, id0 => 1,     idn => 7,
      oracle_where_0 => "", oracle_prefix_0 => "", oracle_suffix_0 => "",
      oracle_where_1 => "", oracle_prefix_1 => "", oracle_suffix_1 => "",
      mysql_suffix_0 => "",
      mysql_suffix_1 => "",
    },
    { startrow => 0, nrows => 7, id0 => 1,     idn => 7,
      oracle_where_0 => "", oracle_prefix_0 => "", oracle_suffix_0 => "",
      oracle_where_1 => "", oracle_prefix_1 => "", oracle_suffix_1 => "",
      mysql_suffix_0 => "",
      mysql_suffix_1 => "",
    },
    {                    endrow => 0, nrows => 7, id0 => 1,     idn => 7,
      oracle_where_0 => "", oracle_prefix_0 => "", oracle_suffix_0 => "",
      oracle_where_1 => "", oracle_prefix_1 => "", oracle_suffix_1 => "",
      mysql_suffix_0 => "",
      mysql_suffix_1 => "",
    },
    {
      nrows => 7, id0 => 1,     idn => 7,
      oracle_where_0 => "", oracle_prefix_0 => "", oracle_suffix_0 => "",
      oracle_where_1 => "", oracle_prefix_1 => "", oracle_suffix_1 => "",
      mysql_suffix_0 => "",
      mysql_suffix_1 => "",
    },
    { startrow => 1,     endrow => undef, nrows => 7, id0 => 1,     idn => 7,
      oracle_where_0 => "", oracle_prefix_0 => "", oracle_suffix_0 => "",
      oracle_where_1 => "", oracle_prefix_1 => "", oracle_suffix_1 => "",
      mysql_suffix_0 => "",
      mysql_suffix_1 => "",
    },
    { startrow => 1, nrows => 7, id0 => 1,     idn => 7,
      oracle_where_0 => "", oracle_prefix_0 => "", oracle_suffix_0 => "",
      oracle_where_1 => "", oracle_prefix_1 => "", oracle_suffix_1 => "",
      mysql_suffix_0 => "",
      mysql_suffix_1 => "",
    },
    { startrow => 1,     endrow => 0, nrows => 7, id0 => 1,     idn => 7,
      oracle_where_0 => "", oracle_prefix_0 => "", oracle_suffix_0 => "",
      oracle_where_1 => "", oracle_prefix_1 => "", oracle_suffix_1 => "",
      mysql_suffix_0 => "",
      mysql_suffix_1 => "",
    },
    { startrow => 0,     endrow => 0, nrows => 7, id0 => 1,     idn => 7,
      oracle_where_0 => "", oracle_prefix_0 => "", oracle_suffix_0 => "",
      oracle_where_1 => "", oracle_prefix_1 => "", oracle_suffix_1 => "",
      mysql_suffix_0 => "",
      mysql_suffix_1 => "",
    },
    { startrow => 0,     endrow => 0, nrows => 7, id0 => 1,     idn => 7,
      oracle_where_0 => "", oracle_prefix_0 => "", oracle_suffix_0 => "",
      oracle_where_1 => "", oracle_prefix_1 => "", oracle_suffix_1 => "",
      mysql_suffix_0 => "",
      mysql_suffix_1 => "",
    },
);

{
    my $expect_sql_template = <<EOF;
PREFIXselect
   person_id
from test_personWHERE
order by
   person_idSUFFIX
EOF
    my ($options, $nrows, $id0, $idn, $msg, $swo_str, $value);
    my $id = $App::options{id};
    my ($select_nrows, $select_id0, $select_idn);
    for (my $swo = 0; $swo <= 1; $swo++) {
        $rep->{select_without_offset} = $swo;
        $swo_str = $swo ? " (without offset)" : " (with offset)";
        for (my $i = 0; $i <= $#options; $i++) {
            next if (defined $id && $i != $id);
            last if (!ref($options[$i]));
            $options = { order_by => [ "person_id" ] };
            $options->{startrow} = $options[$i]{startrow} if (exists $options[$i]{startrow});
            $options->{endrow}   = $options[$i]{endrow}   if (exists $options[$i]{endrow});
            $id0 = $options[$i]{id0};
            $idn = $options[$i]{idn};

            $expect_sql = $expect_sql_template;
            #$expect_sql =~ s/\n$//;
            $value = (exists $options[$i]{"${dbtype}_prefix_${swo}"}) ? $options[$i]{"${dbtype}_prefix_${swo}"} : $options[$i]{"${dbtype}_prefix"};
            $expect_sql =~ s/PREFIX/$value/;
            $value = (exists $options[$i]{"${dbtype}_where_${swo}"}) ? $options[$i]{"${dbtype}_where_${swo}"} : $options[$i]{"${dbtype}_where"};
            $expect_sql =~ s/WHERE/$value/;
            $value = (exists $options[$i]{"${dbtype}_suffix_${swo}"}) ? $options[$i]{"${dbtype}_suffix_${swo}"} : $options[$i]{"${dbtype}_suffix"};
            $expect_sql =~ s/SUFFIX/$value/;

            $msg = "limit/offset [$i] startrow=[$options->{startrow}] endrow=[$options->{endrow}]$swo_str";
            if (defined $id0 && defined $idn) {
                $select_nrows = $swo ? $idn : ($idn - $id0 + 1);
                $select_id0   = $swo ? 1    : $id0;
                $select_idn   = $idn;
            }
            else {
                $select_nrows = undef;
                $select_id0   = undef;
                $select_idn   = undef;
            }

            $sql = $rep->_mk_select_sql("test_person",{},["person_id"],$options);
            is($sql, $expect_sql, "limit/offset [$i] startrow=[$options->{startrow}] endrow=[$options->{endrow}]$swo_str : _mk_select_sql() sql ok");
            &check_select($sql,$select_nrows,$select_id0,$select_idn,$msg);

            $expect_sql =~ s/test_person/test_person p/;
            $expect_sql =~ s/person_id/p.person_id/g;

            $sql = $rep->_mk_select_joined_sql("test_person",{},["person_id"],$options);
            is($sql, $expect_sql, "limit/offset [$i] startrow=[$options->{startrow}] endrow=[$options->{endrow}]$swo_str : _mk_select_joined_sql() sql ok");
            &check_select($sql,$select_nrows,$select_id0,$select_idn,$msg);

            $nrows = $options[$i]{nrows};
            $nrows = 0 if (!defined $nrows);
            &test_get_rows($expect_sql,$nrows,$id0,$idn,$msg,"test_person",{},["person_id"],$options);
        }
    }
    if ($dbtype eq "oracle") {
        my ($row);
        $expect_sql = <<EOF;
select
   count(*) as count_
from test_person p
EOF
        $row = $rep->get_row("test_person", {}, ["count(*)"]);
        $sql = $rep->{sql};
        is($sql, $expect_sql,            "select count(*) limit 1 (Oracle) got right sql");
        ok($row && ref($row) eq "ARRAY", "select count(*) limit 1 (Oracle) got one row");

        $expect_sql = <<EOF;
select a.* from
(select
   p.gender as gnd,
   count(*) as count_
from test_person p
group by
   p.gender
) a where rownum <= 1
EOF
        $row = $rep->get_row("test_person", {}, ["gender", "count(*)"]);
        $sql = $rep->{sql};
        is($sql, $expect_sql,            "select gender, count(*) limit 1 (Oracle) got right sql");
        ok($row && ref($row) eq "ARRAY", "select gender, count(*) limit 1 (Oracle) got one row");

        $expect_sql = <<EOF;
select a.* from
(select
   p.gender as gnd,
   count(*) as count_
from test_person p
group by
   p.gender
) a where rownum <= 1
EOF
        $row = $rep->get_row("test_person", {}, ["gender", "count(*)"], {group_by => ["gender"]});
        $sql = $rep->{sql};
        is($sql, $expect_sql,            "select gender, count(*) limit 1 (Oracle) got right sql");
        ok($row && ref($row) eq "ARRAY", "select gender, count(*) limit 1 (Oracle) got one row");

        $expect_sql = <<EOF;
select a.* from
(select
   p.gender as gnd,
   count(*) as count_
from test_person p
group by
   p.gender
order by
   gnd
) a where rownum <= 1
EOF
        $row = $rep->get_row("test_person", {}, ["gender", "count(*)"], {order_by => ["gender"]});
        $sql = $rep->{sql};
        is($sql, $expect_sql,            "select gender, count(*) limit 1 (Oracle) got right sql");
        ok($row && ref($row) eq "ARRAY", "select gender, count(*) limit 1 (Oracle) got one row");
    }
}

exit 0;

